const pool = require('../config/db');
const bcrypt = require('bcryptjs');

const createUser = async (user) => {
    const hashedPassword = await bcrypt.hash(user.password, 10);
    // 支持邮箱或手机号注册
    const isEmail = !!user.email;
    const columns = isEmail ? 'email, password, role, nickname, avatar' : 'phone, password, role, nickname, avatar';
    const values = isEmail ? 
        [user.email, hashedPassword, user.role || 'user', user.nickname || '', user.avatar || ''] :
        [user.phone, hashedPassword, user.role || 'user', user.nickname || '', user.avatar || ''];
    const [result] = await pool.execute(`INSERT INTO users (${columns}) VALUES (?,?,?,?,?)`, values);
    return result.insertId;
};

const getUserByEmail = async (email) => {
    const [rows] = await pool.execute('SELECT * FROM users WHERE email = ?', [email]);
    return rows[0];
};

const getUserByPhone = async (phone) => {
    const [rows] = await pool.execute('SELECT * FROM users WHERE phone = ?', [phone]);
    return rows[0];
};

const getUserById = async (id) => {
    console.log('[DB] 开始查询用户信息:', { id });
    try {
        const [rows] = await pool.execute(`
            SELECT 
                id,
                id AS userId,
                nickname,
                avatar,
                gas_card_balance AS gasCardBalance,
                phone,
                email,
                role,
                name AS realName,
                created_at AS createdAt,
                updated_at AS updatedAt
            FROM users 
            WHERE id = ?
        `, [id]);

        console.log('[DB] 用户查询结果:', rows[0] || '用户不存在');
        return rows[0];
    } catch (error) {
        console.error('[DB] 查询用户信息失败:', error);
        throw error;
    }
};

const getUserFavorites = async (userId) => {
    const [rows] = await pool.execute(`
        SELECT f.id, s.id as storeId, s.name as storeName, s.cover_image as coverImage, s.type 
        FROM user_collections f
        JOIN stores s ON f.store_id = s.id
        WHERE f.user_id =?
    `, [userId]);
    return rows;
};

const addFavorite = async (userId, storeId, type = 'store') => {
    // 检查是否已收藏
    const [existing] = await pool.execute('SELECT id FROM user_collections WHERE user_id =? AND store_id =?', [userId, storeId]);
    if (existing.length > 0) {
        throw new Error('已收藏该店铺');
    }
    const [result] = await pool.execute('INSERT INTO user_collections (user_id, store_id) VALUES (?,?)', [userId, storeId]);
    return result.insertId;
};

const getUserByOpenid = async (openid) => {
    const [rows] = await pool.execute('SELECT * FROM users WHERE openid =?', [openid]);
    return rows[0];
};

const createUserWithOpenid = async (openid) => {
    // 生成随机密码并哈希处理
    const randomPassword = Math.random().toString(36).slice(-8);
    const hashedPassword = await bcrypt.hash(randomPassword, 10);
    const [result] = await pool.execute(
        'INSERT INTO users (openid, role, nickname, avatar, password) VALUES (?,?,?,?,?)',
        [openid, 'user', '', '', hashedPassword]
    );
    const userId = result.insertId;
    return getUserById(userId);
};

const getGasCardById = async (userId) => {
    const [rows] = await pool.execute(
        'SELECT id as cardId, gas_card_balance as balance FROM users WHERE id = ?',
        [userId]
    );
    return rows[0];
};

const rechargeGasCard = async (userId, amount, paymentMethod) => {
    // 开启事务
    await pool.beginTransaction();
    try {
        // 更新用户加油卡余额
        await pool.execute('UPDATE users SET gas_card_balance = gas_card_balance + ? WHERE id = ?', [amount, userId]);
        // 插入充值记录
        const [result] = await pool.execute(
            'INSERT INTO recharge_records (user_id, amount, payment_method, status) VALUES (?, ?, ?, ?)',
            [userId, amount, paymentMethod, 'completed']
        );
        await pool.commit();
        return { transactionId: result.insertId };
    } catch (error) {
        await pool.rollback();
        throw error;
    }
};

const getRechargeRecords = async (userId, page = 1, size = 10) => {
    const offset = (page - 1) * size;
    const [records] = await pool.execute(
        'SELECT id, amount, payment_method as paymentMethod, status, created_at as time FROM recharge_records WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ?',
        [userId, size, offset]
    );
    const [totalResult] = await pool.execute('SELECT COUNT(*) as total FROM recharge_records WHERE user_id = ?', [userId]);
    return { list: records, total: totalResult[0].total };
};

// 获取用户优惠券
const getUserCoupons = async (userId) => {
    const [coupons] = await pool.execute(
        `SELECT c.id, c.coupon_condition as title, c.amount as discount, c.expire_date as expireDate, uc.is_used as isUsed, uc.received_at as obtainTime
         FROM user_coupons uc
         JOIN coupons c ON uc.coupon_id = c.id
         WHERE uc.user_id = ?
         ORDER BY uc.received_at DESC`,
        [userId]
    );
    return coupons;
};

// 领取优惠券
const receiveCoupon = async (userId, couponId) => {
    // 检查优惠券是否存在且未过期
    const [coupon] = await pool.execute('SELECT * FROM coupons WHERE id = ? AND end_time > NOW()', [couponId]);
    if (coupon.length === 0) {
        throw new Error('优惠券不存在或已过期');
    }

    // 检查用户是否已领取该优惠券
    const [existing] = await pool.execute('SELECT * FROM user_coupons WHERE user_id = ? AND coupon_id = ?', [userId, couponId]);
    if (existing.length > 0) {
        throw new Error('您已领取过该优惠券');
    }

    // 领取优惠券
    const [result] = await pool.execute(
        'INSERT INTO user_coupons (user_id, coupon_id, obtain_time, is_used) VALUES (?, ?, NOW(), 0)',
        [userId, couponId]
    );
    return { id: result.insertId };
};

// 获取优惠券使用记录
const getCouponUsageRecords = async (userId) => {
    const [records] = await pool.execute(
        `SELECT uc.id, c.title, c.discount, o.order_id as orderId, uc.used_time as usedTime
         FROM user_coupons uc
         JOIN coupons c ON uc.coupon_id = c.id
         LEFT JOIN orders o ON uc.order_id = o.id
         WHERE uc.user_id = ? AND uc.is_used = 1
         ORDER BY uc.used_time DESC`,
        [userId]
    );
    return records;
};

// 获取用户订单
const getUserOrders = async (userId, page = 1, size = 10) => {
    const offset = (page - 1) * size;
    const [orders] = await pool.execute(
        `SELECT id, total_amount as totalAmount, status, created_at as createTime, updated_at as updateTime
         FROM orders
         WHERE user_id = ?
         ORDER BY created_at DESC
         LIMIT ? OFFSET ?`,
        [userId, size, offset]
    );
    const [totalResult] = await pool.execute('SELECT COUNT(*) as total FROM orders WHERE user_id = ?', [userId]);
    return { list: orders, total: totalResult[0].total };
};

// 获取订单详情
const getOrderDetail = async (userId, orderId) => {
    const [order] = await pool.execute(
        `SELECT o.id, o.total_amount as totalAmount, o.status, o.created_at as createTime, o.updated_at as updateTime,
         JSON_ARRAYAGG(
             JSON_OBJECT(
                 \'id\', oi.id,
                 \'productId\', oi.product_id,
                 \'name\', oi.product_name,
                 \'price\', oi.price,
                 \'quantity\', oi.quantity
             )
         ) as items
         FROM orders o
         LEFT JOIN order_items oi ON o.id = oi.order_id
         WHERE o.id = ? AND o.user_id = ?
         GROUP BY o.id`,
        [orderId, userId]
    );
    if (order.length === 0) {
        throw new Error('订单不存在');
    }
    return order[0];
};

// 取消订单
const cancelOrder = async (userId, orderId) => {
    const [order] = await pool.execute('SELECT status FROM orders WHERE id = ? AND user_id = ?', [orderId, userId]);
    if (order.length === 0) {
        throw new Error('订单不存在');
    }
    if (order[0].status !== 'pending') {
        throw new Error('只能取消待支付状态的订单');
    }
    await pool.execute('UPDATE orders SET status = \'cancelled\', updated_at = NOW() WHERE id = ? AND user_id = ?', [orderId, userId]);
    return true;
};

// 确认收货
const confirmReceipt = async (userId, orderId) => {
    const [order] = await pool.execute('SELECT status FROM orders WHERE id = ? AND user_id = ?', [orderId, userId]);
    if (order.length === 0) {
        throw new Error('订单不存在');
    }
    if (order[0].status !== 'shipped') {
        throw new Error('只能确认已发货状态的订单');
    }
    await pool.execute('UPDATE orders SET status = \'completed\', updated_at = NOW() WHERE id = ? AND user_id = ?', [orderId, userId]);
    return true;
};

// 获取推荐二维码
const getRecommendationQRCode = async (userId) => {
    // 生成唯一推荐码
    const [user] = await pool.execute('SELECT invite_code FROM users WHERE id = ?', [userId]);
    if (user.length === 0) {
        throw new Error('用户不存在');
    }
    const inviteCode = user[0].invite_code || generateInviteCode(userId);
    // 这里简化处理，实际项目中可能需要调用二维码生成接口
    const qrcodeUrl = `https://example.com/invite?code=${inviteCode}`;
    return { qrcodeUrl, inviteCode };
};

// 生成邀请码
const generateInviteCode = async (userId) => {
    const code = Math.random().toString(36).substring(2, 10).toUpperCase();
    await pool.execute('UPDATE users SET invite_code = ? WHERE id = ?', [code, userId]);
    return code;
};

// 获取推荐记录
const getRecommendationRecords = async (userId) => {
    const [records] = await pool.execute(
        `SELECT u.id, u.phone, u.created_at as registerTime
         FROM users u
         WHERE u.invited_by = ?
         ORDER BY u.created_at DESC`,
        [userId]
    );
    return records;
};

// 提交商家入驻申请
const submitMerchantApplication = async (userId, businessInfo) => {
    console.log('[MODEL] 提交商家入驻申请', { userId, businessName: businessInfo.name });
    
    // 验证必要字段
    const { name, address, contactPhone, contactPerson, storeType, licenseBase64, shopFrontBase64, latitude, longitude } = businessInfo;
    if (!name || !address || !contactPhone || !contactPerson) {
        throw new Error('缺少必要的商家信息');
    }
    
    try {
        // 开始事务
        await pool.query('START TRANSACTION');
        
        // 插入商家申请记录到merchant_applications表
        const [result] = await pool.execute(
            `INSERT INTO merchant_applications 
            (user_id, business_name, address, latitude, longitude, contact_phone, contact_person, store_type, status, apply_time, license_image, shop_front_image) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), ?, ?)`,
            [userId, name, address, latitude || null, longitude || null, contactPhone, contactPerson, storeType || '美食', 'pending', licenseBase64, shopFrontBase64]
        );
        
        // 提交事务
        await pool.query('COMMIT');
        
        console.log('[MODEL] 商家入驻申请提交成功', { userId, applicationId: result.insertId });
        return { applicationId: result.insertId };
    } catch (error) {
        // 回滚事务
        await pool.query('ROLLBACK');
        console.error('[MODEL] 商家入驻申请提交失败', { userId, error: error.message });
        throw error;
    }
};

// 查询商家入驻状态
const getMerchantApplicationStatus = async (userId) => {
    const [application] = await pool.execute(
        `SELECT id, status, apply_time as applyTime, review_time as reviewTime, reject_reason as rejectReason
         FROM merchant_applications
         WHERE user_id = ?
         ORDER BY apply_time DESC
         LIMIT 1`,
        [userId]
    );
    if (application.length === 0) {
        return { status: 'not_applied' };
    }
    return application[0];
};

module.exports = {
    getUserByEmail, createUser, getUserByPhone, getUserById, getUserFavorites, addFavorite, getUserByOpenid, createUserWithOpenid, getGasCardById, rechargeGasCard, getRechargeRecords, getUserCoupons, receiveCoupon, getCouponUsageRecords, getUserOrders, getOrderDetail, cancelOrder, confirmReceipt, getRecommendationQRCode, getRecommendationRecords, submitMerchantApplication, getMerchantApplicationStatus };